# -*- coding: utf-8 -*-
"""
#
# Authors: limanman
# OsChina: http://my.oschina.net/pydevops/
# Purpose:
#
"""
import MySQLdb as mdb
from MySqlPool import Mysql
import datetime
import time
import traceback
from datetime import datetime, timedelta
from logger import logger
import sys
reload(sys)
sys.setdefaultencoding('utf8')

def get_last_data_test():
    mysql = Mysql()
    current_time = time.time() * 1000;
    last_sql = "select * from lamp_info_log where lampid = %s order by updatetime desc limit 1" \
               % (13162)

    last_info_list = mysql.getAll(last_sql)
    month_info = last_info_list[0] if len(last_info_list) > 0 else {}
    print time.time() * 1000 - current_time
    print str(last_sql)
    print str(month_info)

def get_first_data_test():
    mysql = Mysql()
    current_time = time.time() * 1000
    last_sql = "select * from lamp_info_log where lampid = %s order by updatetime limit 1" \
               % (13162)

    last_info_list = mysql.getAll(last_sql)
    month_info = last_info_list[0] if len(last_info_list) > 0 else {}
    print time.time() * 1000 - current_time
    print str(last_sql)
    print str(month_info)

def create_new_lamp(addr, deviceType):

    tblName = 'lampinfo'
    sql_head = "insert into " + tblName + "(projectid, networkid, status, address, createtime) values "
    values = ''

    values += "(" + "0, 0, 0,'" + str(addr) + "','" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "'),"
    if values.endswith(","):
        values  = values[0:-1] + ";"
    if values != '':
        try:
            sql = sql_head + values
            mysql = Mysql()
            print sql
            mysql.insertOne(sql)
        except:
            logger.error(traceback.format_exc())
            return False
    return True

def get_lamp_info_test():
    mysql = Mysql()
    current_time = time.time() * 1000;

    #last_sql = "select a.address, b.id, b.protocoltype from (select * from lampinfo where id in (6452,6458,6515,6528,6529,6530)) lampinfo a" \
    #          "inner JOIN network b where b.id = a.networkid"
    last_sql = "select a.address from lampinfo a inner join lamp_group b on a.id in b.lampid where b.id = '20'"
    #last_sql = "select lampinfo.address, network.id, network.protocoltype from lampinfo, network where network.id = lampinfo.networkid and lampinfo.id in (6452,6458,6515,6528,6529,6530)"
    last_info_list = mysql.getAll(last_sql)
    #month_info = last_info_list[0] if len(last_info_list) > 0 else {}
    print time.time() * 1000 - current_time
    print str(last_sql)

    print str(last_info_list)

def get_lamp_ee():
    mysql = Mysql()
    current_time = time.time() * 1000;

    last_sql = "SELECT electric_info_log.totalgeneration as lastmonthgeneration, electric_info_log.totalconsumption as lastmonthconsumption FROM electric_info_log " \
                "left join lampinfo on electric_info_log.lampid = lampinfo.id where lampinfo.projectid = 13242 and electric_info_log.updatetime < '2018-09-01 00:00:00' ORDER BY electric_info_log.updatetime desc limit 1;"
                

    last_info_list = mysql.getAll(last_sql)
    #month_info = last_info_list[0] if len(last_info_list) > 0 else {}
    print time.time() * 1000 - current_time
    print str(last_sql)

    print str(last_info_list)

def get_lamp_ee_dd():
    mysql = Mysql()
    current_time = time.time() * 1000;

    last_sql = "SELECT electric_info_log.totalgeneration as lastmonthgeneration, electric_info_log.totalconsumption as lastmonthconsumption FROM electric_info_log " \
               "left join lampinfo on electric_info_log.lampid = lampinfo.id where lampinfo.projectid = 14814 and electric_info_log.updatetime < '2018-09-01 00:00:00' ORDER BY electric_info_log.updatetime desc limit 1;"

    last_info_list = mysql.getAll(last_sql)
    # month_info = last_info_list[0] if len(last_info_list) > 0 else {}
    print time.time() * 1000 - current_time
    print str(last_sql)

    print str(last_info_list)

def get_lamp_info_dd():
    mysql = Mysql()
    current_time = time.time() * 1000;

    last_sql = "SELECT `L`.`number`, `L`.`status` as `lampstatus`, `N`.`protocoltype`, `N`.`status` as `status`," \
               "`N`.`rssi`, `N`.`snr`, `LI`.`updatetime`, `L`.`lighteness`, `L`.`address`, `LI`.`lamppower`, `BI`.`chargestage`, `BI`.`battvoltage`, `L`.`isfaulted`, `L`.`section`, `L`.`id`, `L`.`projectid` "\
               "FROM `lampinfo` as `L` "\
                "LEFT JOIN `project` as `P` ON `L`.`projectid` = `P`.`id` "\
            "LEFT JOIN `network` as `N` ON `L`.`networkid` = `N`.`id` "\
            "LEFT JOIN (select lampid,max(updatetime) as time from electric_info_log group by lampid) as T ON `L`.`id` = `T`.`lampid` "\
            "LEFT JOIN `lamp_info_log` as `LI` ON `L`.`id` = `LI`.`lampid` AND `LI`.`lampid`=`T`.`lampid` AND `LI`.`updatetime` = `T`.`time` "\
            "LEFT JOIN `battery_info_log` as `BI` ON `L`.`id` = `BI`.`lampid` AND `BI`.`lampid`=`T`.`lampid` AND `BI`.`updatetime` = `T`.`time` "\
            "LEFT JOIN `history_info_log` as `HI` ON `L`.`id` = `HI`.`lampid` AND `HI`.`lampid`=`T`.`lampid` AND `HI`.`updatetime` = `T`.`time` "\
            "LEFT JOIN (select t1.* from alarm_info_log t1, (select lampid, max(updatetime) as maxtime from alarm_info_log group by lampid) t2 "\
               "where t1.lampid = t2.lampid and t1.updatetime = t2.maxtime) as AI ON `L`.`id` = `AI`.`lampid` "\
            "LEFT JOIN `solar_info_log` as `SI` ON `L`.`id` = `SI`.`lampid` AND `SI`.`lampid`=`T`.`lampid` AND `SI`.`updatetime` = `T`.`time` "\
            "LEFT JOIN `electric_info_log` as `EI` ON `L`.`id` = `EI`.`lampid` AND `EI`.`lampid`=`T`.`lampid` AND `EI`.`updatetime` = `T`.`time` "\
            "LEFT JOIN `system_info_log` as `SIL` ON `L`.`id` = `SIL`.`lampid` AND `SIL`.`lampid`=`T`.`lampid` AND `SIL`.`updatetime` = `T`.`time` "\
            "WHERE `L`.`projectid` = 23816 "\
            "ORDER BY `L`.`number` ASC, `N`.`networkname` ASC  LIMIT 16"

    last_info_list = mysql.getAll(last_sql)
    # month_info = last_info_list[0] if len(last_info_list) > 0 else {}
    print time.time() * 1000 - current_time
    print str(last_sql)

    print str(last_info_list)

def main():
    """Main function."""

    #get_last_data_test()
    #get_first_data_test()
    #get_lamp_info_test()
    get_lamp_ee()
    get_lamp_ee_dd()
    get_lamp_info_dd()

    #create_new_lamp("FF000110")
if __name__ == '__main__':
    main()
